/****** Object:  Table [DW].[dDate]    Script Date: 22.11.2021 14:00:51 ******/
DROP TABLE IF  EXISTS [DW].[dDate]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [DW].[dDate](
	[Date] [date] NULL,
	[Year] [int] NULL,
	[Year Month] [date] NULL,
	[Year Number] [int] NULL,
	[Month] [nvarchar](3) NULL,
	[Month Number] [int] NULL,
	[Day of Week] [varchar](3) NULL,
	[Day of Week Number] [int] NULL,
	[Is Weekend] [int] NOT NULL,
	[IsLastDayOfMonth] [int] NOT NULL,
	[Month Day Number] [int] NULL,
	[Month Days] [int] NULL,
	[Quarter Number] [int] NULL,
	[Quarter] [varchar](2) NULL,
	[Week Number] [int] NULL,
	[Week] [varchar](3) NULL,
	[Year Day Number] [int] NULL,
	[Sequential Day Number] [int] NULL,
	[Weekend] [varchar](7) NOT NULL,
	[DateKEy] [int] NULL,
	[TheDaySuffix] [char](2) NULL,
	[TheISOweek] [int] NULL,
	[TheFirstOfWeek] [date] NULL,
	[TheLastOfWeek] [date] NULL,
	[TheWeekOfMonth] [tinyint] NULL,
	[TheFirstOfQuarter] [date] NULL,
	[TheLastOfQuarter] [date] NULL,
	[TheISOYear] [int] NULL,
	[IsLeapYear] [bit] NULL,
	[Has53Weeks] [int] NOT NULL,
	[Has53ISOWeeks] [int] NOT NULL,
	[YYYYMM] [char](6) NULL,
	[Year Month Number] [int] NULL,
	[Year Quarter Number] [int] NULL,
	[PM Date] [date] NULL,
	[PQ Date] [date] NULL,
	[PY Date] [date] NULL
) ON [PRIMARY]
GO


DROP PROCEDURE IF EXISTS [dbo].[dimMergedDate]
GO


CREATE PROCEDURE [dbo].[dimMergedDate]

AS 
BEGIN

DECLARE @StartDate  date ;
DECLARE @MinStartDate  date = '1984-05-12'; --T-800 Terminator arrived from 2029 to kill Sarah Connor
SET @StartDate =		(SELECT MIN ([Date]) FROM (
						SELECT MIN([Date]) AS [Date] FROM [dbo].[stg$Transaction Header] UNION
						SELECT MIN([Posting Date]) as [Date] FROM [dbo].[stg$Sales Cr_Memo Header] UNION
						SELECT MIN([Posting Date]) as [Date]  FROM [dbo].[stg$Sales Invoice Header]
						) AS u)
Select @StartDate = IIF(@StartDate <= @MinStartDate, @MinStartDate, @StartDate)

DECLARE @CutoffDate date;
DECLARE @MaxCutoffDate date = '2404-02-13'; --Star Trek End game
Select @CutoffDate =	(SELECT MAX ([Date]) FROM (
						SELECT MAX([Date]) AS [Date] FROM [dbo].[stg$Transaction Header] UNION
						SELECT MAX([Posting Date]) as [Date] FROM [dbo].[stg$Sales Cr_Memo Header] UNION
						SELECT MAX([Posting Date]) as [Date]  FROM [dbo].[stg$Sales Invoice Header]
						) AS u)
Select @CutoffDate = IIF(@CutoffDate >= @MaxCutoffDate, @MaxCutoffDate, @CutoffDate)

;WITH seq(n) AS 
(
  SELECT 0 UNION ALL SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d) AS 
(
  SELECT DATEADD(DAY, n, @StartDate) FROM seq
),
src AS
(
  SELECT
    [Date]         = CONVERT(date, d),
    TheDay          = DATEPART(DAY,       d),
    TheDayName      = DATENAME(WEEKDAY,   d),
    TheWeek         = DATEPART(WEEK,      d),
    TheISOWeek      = DATEPART(ISO_WEEK,  d),
    TheDayOfWeek    = DATEPART(WEEKDAY,   d),
    TheMonth        = DATEPART(MONTH,     d),
    TheMonthName    = DATENAME(MONTH,     d),
    TheQuarter      = DATEPART(Quarter,   d),
    TheYear         = DATEPART(YEAR,      d),
    TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
    TheLastOfYear   = DATEFROMPARTS(YEAR(d), 12, 31),
    TheDayOfYear    = DATEPART(DAYOFYEAR, d)
  FROM d
),
dim AS
(
  SELECT
    [Date],
	TheYear													AS [Year],
	MAX([Date]) OVER (PARTITION BY TheYear, TheMonth)		AS [Year Month],
	TheYear													AS [Year Number], --TEST if it is ok to remove this
	LEFT(TheMonthName,3)									AS [Month],
	TheMonth												AS [Month Number],
	LEFT(TheDayOfWeek,3)									AS [Day of Week],
	TheDayOfWeek											AS [Day of Week Number],
	CASE 
		WHEN 
		TheDayOfWeek IN 
		(CASE @@DATEFIRST WHEN 1 THEN 6 WHEN 7 THEN 1 END,7) 
		THEN 1 ELSE 0 END									AS [Is Weekend]  ,
	IIF	(MAX([Date]) 
		OVER (PARTITION BY 
			TheYear, TheMonth) = [Date], 1, 0)				AS [IsLastDayOfMonth], 
	TheDay													AS [Month Day Number],
	DAY(MAX([Date]) OVER (PARTITION BY TheYear, TheMonth))	AS [Month Days], 
	TheQuarter												AS [Quarter Number], 
	'Q' + CAST(TheQuarter AS char(1))						AS [Quarter],
	TheWeek													AS [Week Number], 
	'W' + CAST(TheWeek AS CHAR(2))							AS [Week],
	TheDayOfYear											AS [Year Day Number],
	DATEDIFF(DAY, @StartDate, [Date])						AS [Sequential Day Number],
	CASE 
		WHEN 
		TheDayOfWeek IN 
		(CASE @@DATEFIRST WHEN 1 THEN 6 WHEN 7 THEN 1 END,7) 
		THEN 'Weekend' ELSE 'Workday' END					AS [Weekend],
	YEAR([Date])*10000+MONTH([Date])*100+DAY([Date])		AS [DateKEy],
	--Addidional fields
	
	TheDaySuffix        = CONVERT(char(2), CASE WHEN TheDay / 10 = 1 THEN 'th' ELSE 
                            CASE RIGHT(TheDay, 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd' 
                            WHEN '3' THEN 'rd' ELSE 'th' END END),
    TheISOweek,
    TheFirstOfWeek      = DATEADD(DAY, 1 - TheDayOfWeek, [Date]),
    TheLastOfWeek       = DATEADD(DAY, 6, DATEADD(DAY, 1 - TheDayOfWeek, [Date])),
    TheWeekOfMonth      = CONVERT(tinyint, DENSE_RANK() OVER 
                            (PARTITION BY TheYear, TheMonth ORDER BY TheWeek)),
    TheFirstOfQuarter   = MIN([Date]) OVER (PARTITION BY TheYear, TheQuarter),
    TheLastOfQuarter    = MAX([Date]) OVER (PARTITION BY TheYear, TheQuarter),
    TheISOYear          = TheYear - CASE WHEN TheMonth = 1 AND TheISOWeek > 51 THEN 1 
                            WHEN TheMonth = 12 AND TheISOWeek = 1  THEN -1 ELSE 0 END,      
      IsLeapYear          = CONVERT(bit, CASE WHEN (TheYear % 400 = 0) 
                            OR (TheYear % 4 = 0 AND TheYear % 100 <> 0) 
                            THEN 1 ELSE 0 END),
    Has53Weeks          = CASE WHEN DATEPART(WEEK,     TheLastOfYear) = 53 THEN 1 ELSE 0 END,
    Has53ISOWeeks       = CASE WHEN DATEPART(ISO_WEEK, TheLastOfYear) = 53 THEN 1 ELSE 0 END,
	[YYYYMM]              = CONVERT(char(4), TheYear) + CONVERT(char(2), CONVERT(char(8), [Date], 101)),
	[Year Month Number]	= (TheYear - YEAR(@MinStartDate)) * 12 + TheMonth, 
	[Year Quarter Number]= (TheYear - YEAR(@MinStartDate)) * 4 + TheQuarter,
	[PM Date]			= DATEADD(month, -1, [Date]),
	[PQ Date]			= DATEADD(month, -3, [Date]),
	[PY Date]			= DATEADD(month, -12, [Date])

  FROM src
)


MERGE [DW].[dDate] AS Target USING 
(SELECT [Date]
      ,[Year]
      ,[Year Month]
      ,[Year Number]
      ,[Month]
      ,[Month Number]
      ,[Day of Week]
      ,[Day of Week Number]
      ,[Is Weekend]
      ,[IsLastDayOfMonth]
      ,[Month Day Number]
      ,[Month Days]
      ,[Quarter Number]
      ,[Quarter]
      ,[Week Number]
      ,[Week]
      ,[Year Day Number]
      ,[Sequential Day Number]
      ,[Weekend]
      ,[DateKEy]
      ,[TheDaySuffix]
      ,[TheISOweek]
      ,[TheFirstOfWeek]
      ,[TheLastOfWeek]
      ,[TheWeekOfMonth]
      ,[TheFirstOfQuarter]
      ,[TheLastOfQuarter]
      ,[TheISOYear]
      ,[IsLeapYear]
      ,[Has53Weeks]
      ,[Has53ISOWeeks]
      ,[YYYYMM]
	  ,[Year Month Number]
	,[Year Quarter Number]
	,[PM Date]
	,[PQ Date]	
	,[PY Date]
  FROM dim
  
) AS Source

ON TARGET.[Date] = Source.[Date]
--OPTION (maxrecursion 0)
WHEN MATCHED
	THEN UPDATE
		SET 
		 [Year] = Source.[Year]
		,[Year Month] = Source.[Year Month]
		,[Year Number] = Source.[Year Number]
		,[Month] = Source.[Month]
		,[Month Number] = Source.[Month Number]
		,[Day of Week] = Source.[Day of Week]
		,[Day of Week Number] = Source.[Day of Week Number]
		,[Is Weekend] = Source.[Is Weekend]
		,[IsLastDayOfMonth] = Source.[IsLastDayOfMonth]
		,[Month Day Number] = Source.[Month Day Number]
		,[Month Days] = Source.[Month Days]
		,[Quarter Number] = Source.[Quarter Number]
		,[Quarter] = Source.[Quarter]
		,[Week Number] = Source.[Week Number]
		,[Week] = Source.[Week]
		,[Year Day Number] = Source.[Year Day Number]
		,[Sequential Day Number] = Source.[Sequential Day Number]
		,[Weekend] = Source.[Weekend]
		,[DateKEy] = Source.[DateKEy]
		,[TheDaySuffix] = Source.[TheDaySuffix]
		,[TheISOweek] = Source.[TheISOweek]
		,[TheFirstOfWeek] = Source.[TheFirstOfWeek]
		,[TheLastOfWeek] = Source.[TheLastOfWeek]
		,[TheWeekOfMonth] = Source.[TheWeekOfMonth]
		,[TheFirstOfQuarter] = Source.[TheFirstOfQuarter]
		,[TheLastOfQuarter] = Source.[TheLastOfQuarter]
		,[TheISOYear] = Source.[TheISOYear]
		,[IsLeapYear] = Source.[IsLeapYear]
		,[Has53Weeks] = Source.[Has53Weeks]
		,[Has53ISOWeeks] = Source.[Has53ISOWeeks]
		,[YYYYMM] = Source.[YYYYMM]
		,[Year Month Number] = Source.[Year Month Number]
	,[Year Quarter Number] = Source.[Year Quarter Number]
	,[PM Date] = Source.[PM Date]
	,[PQ Date]	 = Source.[PQ Date]
	,[PY Date] = Source.[PY Date]


WHEN NOT MATCHED BY TARGET
	THEN INSERT ([Date], [Year],
				[Year Month],
				[Year Number],
				[Month],
				[Month Number],
				[Day of Week],
				[Day of Week Number],
				[Is Weekend],
				[IsLastDayOfMonth],
				[Month Day Number],
				[Month Days],
				[Quarter Number],
				[Quarter],
				[Week Number],
				[Week],
				[Year Day Number],
				[Sequential Day Number],
				[Weekend],
				[DateKEy],
				[TheDaySuffix],
				[TheISOweek],
				[TheFirstOfWeek],
				[TheLastOfWeek],
				[TheWeekOfMonth],
				[TheFirstOfQuarter],
				[TheLastOfQuarter],
				[TheISOYear],
				[IsLeapYear],
				[Has53Weeks],
				[Has53ISOWeeks],
				[YYYYMM],
				[Year Month Number],
				[Year Quarter Number],
				[PM Date],
				[PQ Date],	
				[PY Date])
			VALUES (
			Source.[Date],
			Source.[Year],
					Source.[Year Month],
					Source.[Year Number],
					Source.[Month],
					Source.[Month Number],
					Source.[Day of Week],
					Source.[Day of Week Number],
					Source.[Is Weekend],
					Source.[IsLastDayOfMonth],
					Source.[Month Day Number],
					Source.[Month Days],
					Source.[Quarter Number],
					Source.[Quarter],
					Source.[Week Number],
					Source.[Week],
					Source.[Year Day Number],
					Source.[Sequential Day Number],
					Source.[Weekend],
					Source.[DateKEy],
					Source.[TheDaySuffix],
					Source.[TheISOweek],
					Source.[TheFirstOfWeek],
					Source.[TheLastOfWeek],
					Source.[TheWeekOfMonth],
					Source.[TheFirstOfQuarter],
					Source.[TheLastOfQuarter],
					Source.[TheISOYear],
					Source.[IsLeapYear],
					Source.[Has53Weeks],
					Source.[Has53ISOWeeks],
					Source.[YYYYMM],
					Source.[Year Month Number],
					Source.[Year Quarter Number],
					Source.[PM Date],
					Source.[PQ Date],	
					Source.[PY Date])
					

WHEN NOT MATCHED BY SOURCE
	THEN DELETE
	OPTION (MAXRECURSION 0)
;
;
END

SELECT
	'RowCount' = @@rowcount
GO

